package com.lsjenglish.englishstudy.utils;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Lsj
 * @version 1.0
 * @date 2021/7/30 8:53
 */
public class FileToEngligsh {
    public static  List<String[]> fileToDatabase(String strFile) throws IOException {
        File file = new File(strFile);
        BufferedReader bufferedReader = new BufferedReader(new FileReader(file));
        List<String[]> list = new ArrayList<>();
        String tempString;
        while ((tempString = bufferedReader.readLine())!=null){
            String[] strings = new String[3];
            String[] temps = tempString.split("\\.");
            if(temps.length>=3){
                strings[0] = temps[0].strip();
                strings[1] = temps[1].strip();
                StringBuilder allString = new StringBuilder();
                for (int i = 2; i < temps.length; i++) {
                    allString.append(temps[i].strip());
                }
                strings[2] = allString.toString();
                list.add(strings);
            }else if (temps.length==2){
                strings[0] = temps[0].strip();
                String[] groups = temps[1].split(" ");
                strings[1] = groups[0].strip();
                strings[2] = groups[1].strip();
            } else {
                System.out.println("error:"+tempString);
            }
        }
        bufferedReader.close();
        return list;
    }

    /**
     * 文件传输数据库 格式为编号.英语单词.汉语翻译
     * @param args
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        String file = "F:/english1500.txt";
        List<String[]> lists = fileToDatabase(file);
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/englishworld?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=utf8","root","root");
        for (String[] its:lists){
            String sqlString = "insert into english_word_line values (?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sqlString);
            preparedStatement.setInt(1,Integer.parseInt(its[0]));
            preparedStatement.setString(2,its[1]);
            preparedStatement.setString(3,its[2]);
            int result = preparedStatement.executeUpdate();
            System.out.println("插入成功:"+result);
            preparedStatement.close();
        }
    }
}
